package fr.cephb.socialsnp.webapp;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;
import java.util.zip.GZIPInputStream;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceUnit;
import javax.persistence.Query;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;

import com.oreilly.servlet.MultipartRequest;

import fr.cephb.socialsnp.core.Role;
import fr.cephb.socialsnp.core.Scope;
import fr.cephb.socialsnp.core.User;
import fr.cephb.socialsnp.core.DataSet;
import fr.cephb.socialsnp.webapp.form.FormBean;
import fr.cephb.socialsnp.webapp.form.FormParam;
import fr.cephb.socialsnp.webapp.form.SQLValidator;
import fr.cephb.sql.SQLUtilities;
import fr.cephb.util.Cast;

/**
 * SocialSnpController
 * @author lindenb
 *
 */
public class SocialSnpController extends HttpServlet
	{
	private static final long serialVersionUID = 1L;
	
	private static final Pattern IdPATTERN= Pattern.compile("[a-zA-Z][a-zA-Z0-9]*");
	
	
	@Resource(name="jdbc/snpsocialDS")
	private DataSource dataSource;
	
	@PersistenceUnit(unitName="TopLinkPersistenceUnit2")
    private EntityManagerFactory emf;

	
	static private int getIntParameter(HttpServletRequest req,String name,int defaultValue)
		{
		String v= req.getParameter(name);
		if(v==null || !Cast.Integer.isA(v)) return defaultValue;
		return Cast.Integer.cast(v);
		}
	
	static <T> List<T> castList(Class<T> clazz, List<?> src)
		{
		ArrayList<T> array= new ArrayList<T>(src.size());
		for(Object o: src) array.add(clazz.cast(o));
		return array;
		}
	
	@Override
	protected void service(HttpServletRequest req, HttpServletResponse res)
			throws ServletException, IOException
		{
		/** get the user and put it in the request */
		HttpSession session=req.getSession(false);
		User user = null;
		if(session!=null && !session.isNew()) user=(User)session.getAttribute("user");
		if(user==null)
			{
			user=User.ROOT;
			}
		req.setAttribute("user", user);
		String action= req.getParameter("action");
		
		/** translate path as REST */
		if(action==null || action.trim().length()==0)
			{
			res.sendRedirect(req.getContextPath()+"/"+getServletName()+"?action=datasets");
			return;
			}
		
		int start = getIntParameter(req, "start", 0);
		int num = getIntParameter(req, "num", 100);
		List<?> l=null;

		/**
		 * datasets
		 */
		if(action.equals("datasets"))
			{
			Connection con=null;
			EntityManager mgr=null;
			try {
				con= getConnection();
			
				mgr=createEntityManager();
				Query query=mgr.createNamedQuery("datasetAll");
				query.setFirstResult(start);
				query.setMaxResults(num);
				l= castList(DataSet.class,query.getResultList());
				
				req.setAttribute("items", l);
				RequestDispatcher rd= req.getRequestDispatcher("/public/jsp/datasets.jsp");
				rd.forward(req, res);
				return;
				} 
			catch (Exception e)
				{
				throw new ServletException(e);
				}
			finally
				{
				safeClose(con);
				safeClose(mgr);
				}
			}
		else if(action.equals("dataset.create")&& user.isIdentified())
			{
			RequestDispatcher rd= req.getRequestDispatcher("/public/jsp/create-dataset.jsp");
			rd.forward(req, res);
			return;
			}
		
		else if(action.equals("dataset.submit")&& user.isIdentified())
			{
			MultipartRequest multi= new MultipartRequest(req,System.getProperty("java.io.tmpdir"));
			File file=multi.getFile("file");
			if(file==null) throw new ServletException("Cannot get file");
			Connection con=null;
			BufferedReader r=null;
			try
				{
				r= new BufferedReader(new InputStreamReader(new GZIPInputStream(new FileInputStream(file)),"UTF-8"));
				con= getConnection();
				con.setAutoCommit(false);
				String line;
				while((line=r.readLine())!=null)
					{
					
					}
				r.close();
				
				con.commit();
				RequestDispatcher ds =getServletContext().getRequestDispatcher(
						"/todo-marker-list.jsp");
			    ds.forward(req, res);
			    
				}
			catch(Throwable err)
				{
				SQLUtilities.safeRollBack(con);
				throw new ServletException(err);
				}
			finally
				{
				file.delete();
				if(r!=null) try { r.close();} catch(IOException err2) { }
				SQLUtilities.safeSetAutoCommit(con,true);
				SQLUtilities.safeClose(con);
				}

	
			RequestDispatcher rd= req.getRequestDispatcher("/public/jsp/create-dataset.jsp");
			rd.forward(req, res);
			return;
			}
		else if(action.equals("projects")&& user.isIdentified())
			{
			Connection con=null;
			try {
				List<Project> items= new ArrayList<Project>(num);
				con= getConnection();
				PreparedStatement pstmt= con.prepareStatement(
					"select distinct P.id,P.name,P.description,P.scope,U2P.role " +
					"from project as P,user2project as U2P " +
					"where " +
					"(U2P.project_id=P.id and U2P.user_id=?) limit ?,?" +
					" union "+
					" select distinct P.id,P.name,P.description,P.scope,'USER' from project as P "+
					" where P.scope='PUBLIC'"
					);
				pstmt.setLong(1, user.getId());
				pstmt.setLong(2, start);
				pstmt.setLong(3, num);
				ResultSet row= pstmt.executeQuery();
				while(row.next())
					{
					if(items.size()>num) break;
					Project item= new Project(row.getLong(1),row.getString(2));
					item.description= row.getString(3);
					item.scope= Scope.valueOf(row.getString(4));
					item.role= Role.valueOf(row.getString(5));
					items.add(item);
					}
				

				
				req.setAttribute("items",items);
				RequestDispatcher rd= req.getRequestDispatcher("/public/jsp/projects.jsp");
				rd.forward(req, res);
				return;
				}
			catch (SQLException e)
				{
				throw new ServletException(e);
				}
			finally
				{
				safeClose(con);
				}
			}
		/**
		 * project.view
		 */
		else if(action.equals("project.view"))
			{
			EntityManager mgr=null;
			fr.cephb.socialsnp.core.Project project=null;
			Long project_id= getLongParameter(req, "project.id", null);
			if(project_id==null) throw new ServletException("Parameter missing");
			try
				{
				mgr=createEntityManager();
				Query q=mgr.createNamedQuery("project.findByUserProject");
				q.setParameter("project_id",project_id);
				q.setParameter("user_id",user.getId());
				try
					{
					project =(fr.cephb.socialsnp.core.Project)q.getSingleResult();
					}
				catch(NoResultException noErr)
					{
					q=mgr.createNamedQuery("project.findPublicById");
					q.setParameter("project_id",project_id);
					try
						{
						 project =(fr.cephb.socialsnp.core.Project)q.getSingleResult();
						}
					catch(NoResultException noErr2)
						{
						throw new ServletException("You are not allowed to see this project");
						}
					}
				req.setAttribute("project",project);
				q=mgr.createNamedQuery("project.members");
				q.setParameter("project_id",project_id);
				q.setParameter("role",Role.ADMIN);
				req.setAttribute("project.admins",q.getResultList());
				
				q=mgr.createNamedQuery("project.members");
				q.setParameter("project_id",project_id);
				q.setParameter("role",Role.USER);
				req.setAttribute("project.members",q.getResultList());
				}	
			catch(Throwable err)
				{
				throw new ServletException(err);
				}
			finally
				{
				safeClose(mgr);
				}
			RequestDispatcher rd= req.getRequestDispatcher("/public/jsp/project-view.jsp");
			rd.forward(req, res);
			return;
			}
		/**
		 * project.create
		 */
		else if(action.equals("project.create") && user.isIdentified())
			{
			req.setAttribute("project.admins", user.getLogin());
			RequestDispatcher rd= req.getRequestDispatcher("/public/jsp/create-project.jsp");
			rd.forward(req, res);
			return;
			}
		/**
		 * project.submit
		 */
		else if(action.equals("project.submit") && user.isIdentified())
			{
			Connection con=null;
			try {
				con= getConnection();
				Long project_id= getLongParameter(req, "project.id", null);
				
				if(project_id!=null)
					{
					PreparedStatement pstmt= con.prepareStatement(
						"select count(*) " +
						" from user2project as U2P "+
						" where U2P.user_id=? and U2P.project_id=? and " +
						" U2P.role='ADMIN'"
						);
					
					pstmt.setLong(1, user.getId());
					pstmt.setLong(2, project_id);
					if(SQLUtilities.selectOneValue(pstmt.executeQuery(),Number.class).longValue()!=1)
						{
						throw new ServletException("You cannot change this project");
						}
					}
				
				
				FormBean checker= new FormBean();
				
				//name
				FormParam param= new FormParam("project.name");
				if(project_id==null) checker.addParam(param);
				param.setPattern(IdPATTERN);
				param.setMaxLength(fr.cephb.socialsnp.core.Project.NAME_MAX_LENGTH);
				param.setOccurence(1);
				param.addValidator(new NameNotExists("select count(*) from project where name=?"));
				
				//description	
				param= new FormParam("project.desc");
				checker.addParam(param);
				param.setOccurence(1);
				param.setMinLength(1);
				param.setMaxLength(255);
				
				//scope
				param= new FormParam("project.scope");
				checker.addParam(param);
				param.setOccurence(1);
				param.setAllowedValues(Scope.PRIVATE.name(),Scope.PUBLIC.name());
				
				//project list
				param= new FormParam("project.admins");
				checker.addParam(param);
				SplitSqlUserList admins= new SplitSqlUserList();
				param.setOccurence(1);
				param.addValidator(admins);
				
				//members list
				param= new FormParam("project.members");
				checker.addParam(param);
				SplitSqlUserList members= new SplitSqlUserList();
				param.setOccurence(1);
				param.addValidator(members);
				
				
				//validate input
				checker.validate(req,res);
				admins.usersId.add(user.getId());
				members.usersId.removeAll(admins.usersId);
				
				
				if(checker.isValid())
					{
					con.setAutoCommit(false);
					try {
						PreparedStatement pstmt= null;
						
						if(project_id==null)
							{
							
							
							pstmt= con.prepareStatement(
								"insert into project(name,scope,description,creation,creator_id,modified,modifier_id,version) values" +
								"(?,?,?,now(),?,now(),?,1)"
								);
							pstmt.setString(1, checker.getValue("project.name"));
							pstmt.setString(2, checker.getValue("project.scope"));
							pstmt.setString(3, checker.getValue("project.desc"));
							pstmt.setLong(4, user.getId());
							pstmt.setLong(5, user.getId());
							if(pstmt.executeUpdate()!=1) throw new SQLException("Cannot insert ");
							project_id= SQLUtilities.selectLastInsertId(con).longValue();
							
							
							}
						else
							{
							System.err.println("checker.getValue:"+checker.getValue("project.scope"));
							pstmt=con.prepareStatement(
									"update project set scope=?,description=?,modified=now(),modifier_id=?,version=version+1 where id=?"
									);
							pstmt.setString(1, checker.getValue("project.scope"));
							pstmt.setString(2, checker.getValue("project.desc"));
							pstmt.setLong(3, user.getId());
							pstmt.setLong(4, project_id);
							if(pstmt.executeUpdate()!=1) throw new SQLException("Cannot update project");
							
							pstmt= con.prepareStatement("delete from user2project where project_id=?");
							pstmt.setLong(1, project_id);
							pstmt.executeUpdate();
							}
						
						
						pstmt= con.prepareStatement(
								"insert into user2project(user_id,project_id,role,creation,creator_id) values" +
								"(?,?,?,now(),?)"
								);
						for(int step=0;step<2;++step)
							{
							for(Long i:(step==0?admins.usersId:members.usersId))
								{
								pstmt.setLong(1, i);
								pstmt.setLong(2, project_id);
								pstmt.setString(3, (step==0?Role.ADMIN.name():Role.USER.name()));
								pstmt.setLong(4, user.getId());
								if(pstmt.executeUpdate()!=1) throw new SQLException("Cannot insert in user2project");
								}
							}
						
						
						con.commit();
						res.sendRedirect(req.getContextPath()+"/"+getServletName()+"?action=projects");
						return;
						} 
					catch (SQLException err2) {
						con.rollback();
						throw err2;
						}
					finally
						{
						con.setAutoCommit(true);
						}
					
					}
				
			
				
				req.setAttribute("message.text", checker.listErrors());	
				transfertParamToRequest(req, "project.");
				RequestDispatcher rd= req.getRequestDispatcher("/public/jsp/create-project.jsp");
				rd.forward(req, res);
				return;
				}
			catch (SQLException e) {
				throw new ServletException(e);
				}
			finally
				{
				safeClose(con);
				}
			}
		/**
		 * project.submit
		 */
		else if(action.equals("project.edit") && user.isIdentified())
			{
			Connection con=null;
			try {
				con= getConnection();
				Long project_id=getLongParameter(req,"project.id",0L);
				
				PreparedStatement pstmt= con.prepareStatement(
					"select P.name,P.scope,P.description " +
					" from project as P,user2project as U2P "+
					" where U2P.user_id=? and U2P.project_id=P.id and " +
					" P.id=? and U2P.role='ADMIN'"
					);
				boolean found=false;
				pstmt.setLong(1, user.getId());
				pstmt.setLong(2, project_id);
				ResultSet row= pstmt.executeQuery();
				while(row.next())
					{
					found=true;
					req.setAttribute("project.id", String.valueOf(project_id));
					req.setAttribute("project.name",  row.getString(1));
					req.setAttribute("project.scope", row.getString(2));
					req.setAttribute("project.scope.index", scopeIndex(row.getString(2)));
					req.setAttribute("project.desc",  row.getString(3));
					}
				
				if(!found)
					{
					throw new SQLException("You cannot edit project id:"+project_id);
					}
				
				pstmt= con.prepareStatement("select U.login " +
						" from user as U, user2project as U2P "+
						" where U2P.user_id=U.id and U2P.project_id=? and "+
						"U2P.role=? order by U.login");
				
				for(int step=0;step<2;++step)
					{
					StringBuilder b= new StringBuilder();
					pstmt.setLong(1, project_id.longValue());
					pstmt.setString(2,step==0?Role.ADMIN.name():Role.USER.name());
					row=pstmt.executeQuery();
					while(row.next())
						{
						if(b.length()>0) b.append("\n");
						b.append(row.getString(1));
						}
					req.setAttribute("project."+(step==0?"admins":"members"),  b.toString());
					}

				RequestDispatcher rd= req.getRequestDispatcher("/public/jsp/create-project.jsp");
				rd.forward(req, res);
				return;
				}
			catch (SQLException e) {
				throw new ServletException(e);
				}
			finally
				{
				safeClose(con);
				}
			}
		else if(action.equals("create.dataset") && user.isIdentified())
			{
			Connection con=null;
			try {
				con= getConnection();
				/** select projects */
				List<NamedId.Long> projects=listOwnedProjects(user,0,Integer.MAX_VALUE);
				req.setAttribute("projects", projects);
				RequestDispatcher rd= req.getRequestDispatcher("/public/jsp/create-dataset.jsp");
				rd.forward(req, res);
				return;
			} catch (SQLException e) {
				throw new ServletException(e);
				}
			finally
				{
				safeClose(con);
				}
			}
		/**
		 * 
		 * logout
		 *
		 */
		else if(action.equals("logout"))
			{
			if(user.isIdentified())
				{
				req.setAttribute("message.text", "logged of from "+user.getLogin());
				}
			session=req.getSession();
			session.invalidate();
			res.sendRedirect(req.getContextPath()+"/"+getServletName()+"?action=datasets");
			return;
			}
		else if(action.equals("test"))
			{
			PrintWriter out= res.getWriter();
			out.println("<html><body>"+action);
			out.println(user.getLogin());
			out.println("<br/>pathinfo:"+req.getPathInfo());
			out.println("<br/>getContextPath:"+req.getContextPath());
			out.println("<br/>getServerName:"+req.getServerName());
			out.println("<br/>getQueryString:"+req.getQueryString());
			out.println("<br/>getRequestURI:"+req.getRequestURI());
			if(l!=null) out.println(l);
			out.println("</body></html>");
			out.flush();
			}
		else
			{
			throw new ServletException("Unknown action:"+action);
			}
		
		}
	
	private EntityManager createEntityManager()
		{
		return emf.createEntityManager();
		}
	
	private Connection getConnection() throws SQLException
		{
		return this.dataSource.getConnection();
		}

	
	private void transfertParamToRequest(HttpServletRequest req,String prefix) throws ServletException
		{
		for(Enumeration<String> iter=req.getParameterNames();iter.hasMoreElements();)
			{
			String name=iter.nextElement();
			if(!name.startsWith(prefix)) continue;
			String value= req.getParameter(name);
			req.setAttribute(name, value);
			}
		}
	
	
	
	private List<NamedId.Long> listOwnedProjects(User user,int start,int count) throws SQLException
		{
		if(user==null ) user=User.ANONYMOUS;
		return listNamedItems(
				"select distinct P.id,P.name " +
				"from project as P,user2project as U2P " +
				"where " +
				"U2P.project_id=P.id and U2P.user_id=?",
				user.getId(),
				start,count
				); 
		}
	
	
	private List<NamedId.Long> listVisibleProjects(User user,int start,int count) throws SQLException
		{
		if(user==null ) user=User.ANONYMOUS;
		return listNamedItems(
				"select distinct P.id,P.name " +
				"from project as P,user2project as U2P " +
				"where " +
				"U2P.project_id=P.id and U2P.user_id=?",
				user.getId(),
				start,count
				); 
		}
	private List<NamedId.Long> listNamedItems(String sql,long id,int start,int count) throws SQLException
		{
		Connection con=null;
		try {
			con= getConnection();
			PreparedStatement pstmt= con.prepareStatement(sql); 
			pstmt.setLong(1, id);
			return makeNamedList(pstmt.executeQuery());
			}
		finally
			{
			safeClose(con);
			}
		}
	
	List<NamedId.Long> makeNamedList(ResultSet row) throws SQLException
		{
		ArrayList<NamedId.Long> array= new ArrayList<NamedId.Long>();
		while(row.next())
			{
			array.add(new NamedId.Long(row.getLong(1),row.getString(2)));
			}
		return array;
		}
	
	
	private static String getParameter(HttpServletRequest req,String name,String defaultValue)
		{
		String v= req.getParameter(name);
		return v==null?defaultValue:v;
		}
	
	private static Long getLongParameter(HttpServletRequest req,String name,Long defaultValue)
		{
		String v= req.getParameter(name);
		try {
			Long L= new Long(v.trim());
			return L;
			}
		catch (Exception e) {
			//
			}
		return defaultValue;
		}
	
	
	private void safeClose(Connection con)
		{
		if(con==null) return;
		try {
			con.close();
			} 
		catch (SQLException e)
			{
			
			}
		}
	
	private static int scopeIndex(String s)
		{
		int i=0;
		for(Scope scope:Scope.values())
			{
			if(scope.name().equalsIgnoreCase(s)) return i;
			++i;
			}
		return -1;
		}
	
	
	private void safeClose(EntityManager em)
		{
		if(em==null) return;
		try {
			em.close();
			} 
		catch (Throwable e)
			{
			
			}
		}
	
	private class SplitSqlUserList
		extends SQLValidator
		{	
		private Set<Long> usersId=new HashSet<Long>();
		public SplitSqlUserList()
			{
			super(SocialSnpController.this.dataSource);
			}
		
		@Override
		protected void validate(Connection con, FormParam param)
				throws SQLException
			{
			this.usersId.clear();
			Set<String> persons= new HashSet<String>();
			for(String s: param.getValue().split("[\n \r\t,;]+"))
				{
				if(s.length()==0) continue;
				persons.add(s);
				}
			persons.remove("anonymous");//not in admins
			PreparedStatement pstmt= con.prepareStatement("select id from user where login=?");
			for(String s:persons)
				{
				pstmt.setString(1, s);
				Number user_id= SQLUtilities.selectOneOrZeroValue(pstmt.executeQuery(),Number.class);
				if(user_id==null)
					{
					param.addError("Unknown user "+s);
					break;
					}
				this.usersId.add(user_id.longValue());
				}
			pstmt.close();
			}
		}
	
	/**
	 * 
	 * NameNotExists
	 * @author lindenb
	 *
	 */
	private class NameNotExists
	extends SQLValidator
		{	
		private String countsql;
		public NameNotExists(String countsql)
			{
			super(SocialSnpController.this.dataSource);
			this.countsql=countsql;
			}
		
		@Override
		protected void validate(Connection con, FormParam param)
				throws SQLException
			{
			PreparedStatement pstmt= con.prepareStatement(this.countsql);
			for(String s:param.getValues())
				{
				pstmt.setString(1, s.trim());
				Number count= SQLUtilities.selectOneOrZeroValue(pstmt.executeQuery(),Number.class);
				if(count.intValue()!=0)
					{
					param.addError("value already exists "+s);
					break;
					}
				}
			pstmt.close();
			}
		}
	
	/**
	 * 
	 * Project
	 */
	public static class Project extends NamedId.Long
		{
		private String description;
		private Scope scope;
		private Role role;
		public Project(long id,String name)
			{
			super(id,name);
			}
		public String getDescription() {
			return description;
			}
		public Scope getScope() {
			return scope;
			}
		public Role getRole() {
			return role;
			}
		public boolean isAdmin()
			{
			return getRole()==Role.ADMIN;
			}
		}
	}
